﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Runtime = Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.EntityClient;
using System.Data.SqlClient;
using SSISFromCode.Helpers;
using System.IO;

namespace SSISFromCode
{

    /// <summary>
    /// The Excel packages run fine, but won't currently be editable in the SSIS designer if written to package file and opened.
    /// The package is the same as would be created using solution 1 in this post: http://hrvoje.piasevoli.com/2010/09/01/importing-data-from-64-bit-excel-in-ssis
    /// See also http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/c6eb8d5d-a65c-4fe1-8902-79253bf2f4be
    /// Note also the connection string: Constants.EXCEL_CONNECTION_STRINGS
    /// </summary>
    internal class ExcelGenerator : NonFlatFileGenerator, IRequireSourceConnection
    {

        protected override void SetSourceProperties()
        {
            base.SetSourceProperties();
            _dataSourceInstance.SetComponentProperty( COMPONENT_PROPERTY_NAMES.WORKSHEET, ( (ExcelImportOptions)_options ).Worksheet );
        }

        protected override void CreateDataFlowSourceComponent( Runtime.Application app )
        {
            CreateDataFlowSourceComponent( app, OBJECT_CREATION.EXCEL_SOURCE );
        }

        protected override void CreateConnections()
        {
            base.CreateConnections();
            _connectionSource = _package.Connections.Add( OBJECT_CREATION.OLEDB_CONNECTION );
        }

        void IRequireSourceConnection.ConfigureSourceConnection()
        {
            _connectionSource.Properties[COMMON_PROPERTY_NAMES.NAME].SetValue( _connectionSource, COMPONENT_NAMES.EXCEL_CONNECTION );
            _connectionSource.DelayValidation = true;
            SetConnectionString();
        }

        private void SetConnectionString()
        {
            var headerStr = ( (ExcelImportOptions)_options ).ColumnNamesInFirstRow ? "YES" : "NO";
            var conStr = Path.GetExtension( _options.Path ).ToUpper() == EXCEL_CONNECTION_STRINGS.XLSX_EXTENSION ? EXCEL_CONNECTION_STRINGS.XLSX : EXCEL_CONNECTION_STRINGS.XLS;
            _connectionSource.Properties[COMMON_PROPERTY_NAMES.CONNECTION_STR].SetValue( _connectionSource, string.Format( conStr, _options.Path, headerStr ) );
        }

    }

}
